﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataAccess.DBConnections;
using DataAccess.Interface;
using DataAccess.ProxyConnections;
using Oracle.DataAccess.Client;

namespace DataAccess.Dal
{
    internal class ApplicationDal : IResponsible<Prop.Application>
    {

        private static DBConnectionsEngine dbEngine;
        private static OracleConnection Conn;
        private static Hashtable hashtable;
        private static ApplicationDal _dao;
        private string sql = "";

        public static ApplicationDal GetInstance()
        {
            _dao = new ApplicationDal();
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().GetAuthConnection();
            return _dao;
        }

        public bool Add(Prop.Application obj)
        {
            Boolean result = false;
            try
            {
                sql = System.String.Format(@"
                    INSERT INTO APPLICATION(COMPANY_CODE ,PRIVATE_FLAG ,MC_FLAG ,BUYER_FLAG 
                    ,NT_FLAG ,REMARK ,APPLNO ,APPLNAME ,DESCRIPTION ,CRDATE ,CRBY 
                    ,UPDDATE ,UPDBY ,URL ,PDAFLAG ,MOBILEURL ,STATUS )
                    VALUES(:COMPANY_CODE ,:PRIVATE_FLAG ,:MC_FLAG ,:BUYER_FLAG ,:NT_FLAG 
                    ,:REMARK ,:APPLNO ,:APPLNAME ,:DESCRIPTION ,:CRDATE ,:CRBY ,:UPDDATE 
                    ,:UPDBY ,:URL ,:PDAFLAG ,:MOBILEURL ,:STATUS )"
                    );

                hashtable = new Hashtable();
                hashtable.Add(":COMPANY_CODE", obj.COMPANY_CODE);
                hashtable.Add(":PRIVATE_FLAG", obj.PRIVATE_FLAG);
                hashtable.Add(":MC_FLAG", obj.MC_FLAG);
                hashtable.Add(":BUYER_FLAG", obj.BUYER_FLAG);
                hashtable.Add(":NT_FLAG", obj.NT_FLAG);
                hashtable.Add(":REMARK", obj.REMARK);
                hashtable.Add(":APPLNO", obj.APPLNO);
                hashtable.Add(":APPLNAME", obj.APPLNAME);
                hashtable.Add(":DESCRIPTION", obj.DESCRIPTION);
                hashtable.Add(":CRDATE", obj.CRDATE);
                hashtable.Add(":CRBY", obj.CRBY);
                hashtable.Add(":UPDDATE", obj.UPDDATE);
                hashtable.Add(":UPDBY", obj.UPDBY);
                hashtable.Add(":URL", obj.URL);
                hashtable.Add(":PDAFLAG", obj.PDAFLAG);
                hashtable.Add(":MOBILEURL", obj.MOBILEURL);
                hashtable.Add(":STATUS", obj.STATUS);

                dbEngine.ExecuteNonQuery(sql,hashtable, Conn);

            }
            catch (Exception ex)
            {
                result = true;
            }
            return result;
        }

        public bool Update(Prop.Application obj)
		{
			Boolean result = false;

            StringBuilder concast = new StringBuilder();
			try
			{
				sql = System.String.Format(@" UPDATE APPLICATION
					SET PRIVATE_FLAG=:PRIVATE_FLAG ,
					MC_FLAG=:MC_FLAG ,
					BUYER_FLAG=:BUYER_FLAG ,
					NT_FLAG=:NT_FLAG ,
					REMARK=:REMARK ,
					APPLNAME=:APPLNAME ,
					DESCRIPTION=:DESCRIPTION ,
					UPDDATE=:UPDDATE ,
					UPDBY=:UPDBY ,
					URL=:URL ,
					PDAFLAG=:PDAFLAG ,
					MOBILEURL=:MOBILEURL ,
					STATUS=:STATUS 
                    WHERE COMPANY_CODE=:COMPANY_CODE AND APPLNO=:APPLNO
					");
                hashtable = new Hashtable();
                hashtable.Add(":COMPANY_CODE", obj.COMPANY_CODE);
                hashtable.Add(":PRIVATE_FLAG", obj.PRIVATE_FLAG);
                hashtable.Add(":MC_FLAG", obj.MC_FLAG);
                hashtable.Add(":BUYER_FLAG", obj.BUYER_FLAG);
                hashtable.Add(":NT_FLAG", obj.NT_FLAG);
                hashtable.Add(":REMARK", obj.REMARK);
                hashtable.Add(":APPLNO", obj.APPLNO);
                hashtable.Add(":APPLNAME", obj.APPLNAME);
                hashtable.Add(":DESCRIPTION", obj.DESCRIPTION);
                hashtable.Add(":UPDDATE", obj.UPDDATE);
                hashtable.Add(":UPDBY", obj.UPDBY);
                hashtable.Add(":URL", obj.URL);
                hashtable.Add(":PDAFLAG", obj.PDAFLAG);
                hashtable.Add(":MOBILEURL", obj.MOBILEURL);
                hashtable.Add(":STATUS", obj.STATUS);

			    if (!String.IsNullOrEmpty(obj.APPLNO))
			    {
			        
			    }

                dbEngine.ExecuteNonQuery(sql, hashtable, Conn);

			}
			catch (Exception ex)
			{
				result = true;
			}
			return result;
		}

        public bool Delete(Prop.Application obj)
        {
            Boolean result = false;
            try
            {
                sql = System.String.Format(@" DELETE FROM APPLICATION 
                WHERE COMPANY_CODE=:COMPANY_CODE AND APPLNO=:APPLNO ");

                hashtable = new Hashtable();
                hashtable.Add(":COMPANY_CODE", obj.COMPANY_CODE);
                hashtable.Add(":APPLNO", obj.APPLNO);

                dbEngine.ExecuteNonQuery(sql, hashtable, Conn);

            }
            catch (Exception ex)
            {
                result = true;
            }
            return result;
        }

        public Prop.Application Select(Prop.Application obj)
        {
            Prop.Application entity = new Prop.Application();
            try
            {
                StringBuilder concast = new StringBuilder();
                concast.Append(" WHERE 1=1 ");

                hashtable = new Hashtable();

                if (obj != null)
                {
                    if (!string.IsNullOrEmpty(obj.COMPANY_CODE))
                    {
                        concast.Append(" AND COMPANY_CODE=:COMPANY_CODE ");
                        hashtable.Add(":COMPANY_CODE", obj.COMPANY_CODE);
                    }

                    if (!string.IsNullOrEmpty(obj.APPLNO))
                    {
                        concast.Append(" AND APPLNO=:APPLNO ");
                        hashtable.Add(":APPLNO", obj.APPLNO);
                    }

                    if (!string.IsNullOrEmpty(obj.APPLNAME))
                    {
                        concast.Append(" AND APPLNAME=:APPLNAME ");
                        hashtable.Add(":APPLNAME", obj.APPLNAME);
                    }
                }

                sql = System.String.Format(@"
                            SELECT COMPANY_CODE ,PRIVATE_FLAG ,MC_FLAG ,BUYER_FLAG 
                            ,NT_FLAG ,REMARK ,APPLNO ,APPLNAME ,DESCRIPTION ,CRDATE 
                            ,CRBY ,UPDDATE ,UPDBY ,URL ,PDAFLAG ,MOBILEURL ,STATUS  
                            FROM APPLICATION");

                sql = sql + concast;
                
                entity = dbEngine.ExecuteSingle<Prop.Application>(sql, hashtable, Conn);
            }
            catch (Exception ex)
            {
                entity = null;
            }
            return entity;
        }

        public List<Prop.Application> SelectAll(Prop.Application obj = null)
        {
            List<Prop.Application> entities = new List<Prop.Application>();
            try
            {
                StringBuilder concast = new StringBuilder();
                concast.Append(" WHERE 1=1 ");

                hashtable = new Hashtable();

                if (obj != null)
                {
                    if (!string.IsNullOrEmpty(obj.COMPANY_CODE))
                    {
                        concast.Append(" AND COMPANY_CODE=:COMPANY_CODE ");
                        hashtable.Add(":COMPANY_CODE", obj.COMPANY_CODE);
                    }

                    if(!string.IsNullOrEmpty(obj.APPLNO))
                    {
                        concast.Append(" AND APPLNO=:APPLNO ");
                        hashtable.Add(":APPLNO", obj.APPLNO);
                    }

                    if (!string.IsNullOrEmpty(obj.APPLNAME))
                    {
                        concast.Append(" AND APPLNAME=:APPLNAME ");
                        hashtable.Add(":APPLNAME", obj.APPLNAME);
                    } 
                }

                sql = System.String.Format(@"
                            SELECT COMPANY_CODE ,PRIVATE_FLAG ,MC_FLAG ,BUYER_FLAG 
                            ,NT_FLAG ,REMARK ,APPLNO ,APPLNAME ,DESCRIPTION ,CRDATE 
                            ,CRBY ,UPDDATE ,UPDBY ,URL ,PDAFLAG ,MOBILEURL --,STATUS  
                            FROM APPLICATION");

                sql = sql + concast;

                //DataTable dt = dbEngine.GetDataTable(sql, hashtable, Conn);

                entities = dbEngine.ToList<Prop.Application>(sql, hashtable, Conn);

            }
            catch (Exception ex)
            {
                entities = null;
            }
            return entities;
        }

        public DataTable SelectDt(Prop.Application obj = null)
        {
            try
            {
                StringBuilder concast = new StringBuilder();
                concast.Append(" WHERE 1=1 ");

                hashtable = new Hashtable();

                if (obj != null)
                {
                    if (!string.IsNullOrEmpty(obj.COMPANY_CODE))
                    {
                        concast.Append(" AND COMPANY_CODE=:COMPANY_CODE ");
                        hashtable.Add(":COMPANY_CODE", obj.COMPANY_CODE);
                    }

                    if (!string.IsNullOrEmpty(obj.APPLNO))
                    {
                        concast.Append(" AND APPLNO=:APPLNO ");
                        hashtable.Add(":APPLNO", obj.APPLNO);
                    }

                    if (!string.IsNullOrEmpty(obj.APPLNAME))
                    {
                        concast.Append(" AND APPLNAME=:APPLNAME ");
                        hashtable.Add(":APPLNAME", obj.APPLNAME);
                    }
                }

                sql = System.String.Format(@"
                            SELECT COMPANY_CODE ,PRIVATE_FLAG ,MC_FLAG ,BUYER_FLAG 
                            ,NT_FLAG ,REMARK ,APPLNO ,APPLNAME ,DESCRIPTION ,CRDATE 
                            ,CRBY ,UPDDATE ,UPDBY ,URL ,PDAFLAG ,MOBILEURL --,STATUS  
                            FROM APPLICATION");

                sql = sql + concast;

                //DataTable dt = dbEngine.GetDataTable(sql, hashtable, Conn);

                return dbEngine.GetDataTable(sql, hashtable, Conn);

            }
            catch (Exception ex)
            {
                // = null;
            }
            return null;
        }

    }
}
